package com.netistate.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.netistate.base.BaseDao;
import com.netistate.pojo.Labor;
import com.netistate.pojo.LaborStat;
import com.netistate.pojo.Project;
import com.netistate.pojo.User;

@Repository
public class LaborDao extends BaseDao<Labor, Integer>{

	
	private List<LaborStat> lsList = new ArrayList<LaborStat>();
	
	/**
	 * 根据传来的条件去查询工时
	 * @param projectName
	 * @param laborUser
	 * @param state
	 * @param beginTime
	 * @param endTime
	 * @param section
	 * @param isLocale
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findLaborByCondition(String projectName,
			String laborUser, String state, String beginTime, String endTime,
			String section, String isLocale) {
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		beginTime = beginTime+" 24:00:00";
		
		if("现在".equals(endTime)){
	    	  Date date = new Date();
	    	  endTime = df.format(date);
	    }
		endTime = endTime+" 24:00:00";
		int locale = 0;
		int uid = 0;
		int pid = 0;
		
	    String sql = "select * from t_labor ";
	    if("所有".equals(projectName)){
	    	if("所有".equals(laborUser)){
	    		if("所有".equals(state)){
	    			if("所有".equals(section)){
	    				sql=sql+" where createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if(!"不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"where section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}else{
	    			sql = sql +"where projectProcess ="+state;
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}
	    	}else{
	    		//User u = new UserDao().findById(Integer.parseInt(laborUser));
	    		uid = Integer.parseInt(laborUser);
	    		sql = sql + "where userid =" + uid;
	    		if("所有".equals(state)){
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}else{
	    			sql = sql +"and projectProcess ="+state;
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}
	    	}
	    }else{
	    	//Project p = new ProjectDao().findById(Integer.parseInt(projectName));
	    	pid = Integer.parseInt(projectName);
	    	sql = sql + "where projectid =" + pid;
	    	if("所有".equals(laborUser)){
	    		if("所有".equals(state)){
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}else{
	    			sql = sql +"and projectProcess ="+state;
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}
	    	}else{
	    		//User u = new UserDao().findById(Integer.parseInt(laborUser));
	    		sql = sql + "and userid =" + uid;
	    		if("所有".equals(state)){
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}else{
	    			sql = sql +"and projectProcess ="+state;
	    			if("所有".equals(section)){
	    				sql=sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}else{
	    				sql = sql +"and section =" + section;
	    				sql = sql+" and createtime>='"+beginTime+"' and createtime<='"+endTime+"'";
	    				if("不限".equals(isLocale)){
	    					sql = sql;
	    				}else{
	    					locale = Integer.parseInt(isLocale);
	    					sql=sql+" and locale="+locale;
	    				}
	    			}
	    		}
	    	}
	    }
	    Query query = getSession().createSQLQuery(sql);
	    List<Labor> lList = (List<Labor>)query.list();
	    
	    for(Object o : lList){
	    	
	    }
	    
	    /*for(Object obj : query.list()){
	    	//Object objs[]=(Object[]) obj;
	    	lList.add((Labor) obj);
	    }*/
		return lList;
	}

	/**
	 * 用户和项目为所有时候，只需判断state,section,islocale
	 * @param state
	 * @param beginTime
	 * @param endTime
	 * @param section
	 * @param isLocale
	 * @return
	 */
	public List<Labor> findLaborByCondition(String state, String beginTime,
			String endTime, String section, String isLocale) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		beginTime = beginTime;
		
		if("现在".equals(endTime)){
	    	  Date date = new Date();
	    	  endTime = df.format(date);
	    }
		endTime = endTime;
		int locale = 0;
		String sql = "from Labor ";
		if("所有".equals(state)){
			if("所有".equals(section)){
				sql=sql+" where datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc" ;
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale="+locale;
				}
			}else{
				sql = sql +"where section ='" + section +"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale = '" + locale +"'";
				}
			}
		}else{
			sql = sql +"where projectProcess = '" + state + "'";
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale = '" + locale +"'";
				}
			}else{
				sql = sql +"and section = '" + section + "'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale= '" + locale + "'";
				}
			}
		}
		 Query query = getSession().createQuery(sql);
		return query.list();
	}

	/**
	 * 用户为某个id时,项目为所有,只判断uid,和state,section,islocale
	 * @param u
	 * @param state
	 * @param beginTime
	 * @param endTime
	 * @param section
	 * @param isLocale
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findLaborByCondition(User u, String state,
			String beginTime, String endTime, String section, String isLocale) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		beginTime = beginTime;
		
		if("现在".equals(endTime)){
	    	  Date date = new Date();
	    	  endTime = df.format(date);
	    }
		endTime = endTime;
		int locale = 0;
		String sql = "from Labor ";
		sql = sql + "where user = '" + u.getId() + "'";
		if("所有".equals(state)){
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale = '" + locale +"'";
				}
			}else{
				sql = sql +"and section = '" + section +"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}else{
			sql = sql +"and projectProcess ='"+state+"'";
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}else{
				sql = sql +"and section ='" + section+"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}
		 Query query = getSession().createQuery(sql);
			return query.list();
	}

	/**
	 * //用户为所有时候,只判断pid和state,section,islocale
	 * @param p
	 * @param state
	 * @param beginTime
	 * @param endTime
	 * @param section
	 * @param isLocale
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findLaborByCondition(Project p, String state,
			String beginTime, String endTime, String section, String isLocale) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		beginTime = beginTime;
		
		if("现在".equals(endTime)){
	    	  Date date = new Date();
	    	  endTime = df.format(date);
	    }
		endTime = endTime;
		int locale = 0;
		String sql = "from Labor ";
		sql = sql + "where project = '" + p.getId() +"'";
		if("所有".equals(state)){
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}else{
				sql = sql +"and section ='" + section+"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}else{
			sql = sql +"and projectProcess ='"+state+"'";
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}else{
				sql = sql +"and section ='" + section+"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}
		 Query query = getSession().createQuery(sql);
			return query.list();
	}

	/**
	 * 用户为某个id时，两个都是id，判断pid  uid state,section,islocale
	 * @param p
	 * @param u
	 * @param state
	 * @param beginTime
	 * @param endTime
	 * @param section
	 * @param isLocale
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findLaborByCondition(Project p, User u, String state,
			String beginTime, String endTime, String section, String isLocale) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		beginTime = beginTime;
		
		if("现在".equals(endTime)){
	    	  Date date = new Date();
	    	  endTime = df.format(date);
	    }
		endTime = endTime;
		int locale = 0;
		String sql = "from Labor where user = '"+ u.getId() +"' and project = '" + p.getId() +"'";
		if("所有".equals(state)){
			if("所有".equals(section)){
				sql=sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}else{
				sql = sql +"and section ='" + section+"'";
				sql = sql+" and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}else{
			sql = sql +"and projectProcess ='"+state+"'";
			if("所有".equals(section)){
				sql=sql+"' and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}else{
				sql = sql +"and section ='" + section+"'";
				sql = sql+"' and datetime>='"+beginTime+"' and datetime<='"+endTime+"' order by datetime desc";
				if("不限".equals(isLocale)){
					sql = sql;
				}else{
					locale = Integer.parseInt(isLocale);
					sql=sql+" and locale='"+locale+"'";
				}
			}
		}
		 Query query = getSession().createQuery(sql);
		return query.list();
		
	}

	/**
	 * 工时统计的内容
	 * @param p
	 * @return
	 */
	public List<LaborStat> findLaborStat(Project p) {
		
		String hql = "select sum(l.hours),sum(l.locale),l.section,l.projectProcess from Labor l where l.project = ? group by l.projectProcess";
		
		Query query = getSession().createQuery(hql);
		query.setParameter(0, p.getId());
		@SuppressWarnings("rawtypes")
		List list= query.list();
		
		List<LaborStat> lssStat = new ArrayList<LaborStat>();
		for(int i = 0; i < list.size(); i++){
			LaborStat ls = new LaborStat();
				Object[] obj = (Object[])list.get(i);
				ls.setProjectName(p.getProjectName());
				ls.setWorkTime(obj[0]+"");
				ls.setCurrentPerson(obj[1]+"" );
				ls.setSection(obj[2]+"");
				ls.setProjectProcess(obj[3]+"");
			lssStat.add(ls);
		}
		return lssStat;
	}

	/**
	 * 根据内容去找工时统计，当项目为 所有 的时候
	 * @param p
	 * @param projectNameBySection
	 * @param section
	 * @param sectionHours
	 * @param sectionPerson
	 * @return
	 */
	public List<LaborStat> findLaborStatBySection(Project p,
			 String section, String sectionHours,
			String sectionPerson) {
		String hql = "select sum(hours),sum(locale),section,projectProcess from Labor where projectid = ?";
		
		if("所有".equals(section)){
			if("".equals(sectionPerson)){
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}else{
				hql = hql + " and sum(locale) = '" + sectionPerson + "' ";
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}
		}else{
			hql = hql + " and section = '" + section + "'";
			if("".equals(sectionPerson)){
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}else{
				hql = hql + " and sum(locale) = '" + sectionPerson + "' ";
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}
		}
		Query query = getSession().createQuery(hql);
		query.setParameter(0, p.getId());
		
		@SuppressWarnings("rawtypes")
		List list= query.list();
		
		List<LaborStat> lssStat = new ArrayList<LaborStat>();
		for(int i = 0; i < list.size(); i++){
			LaborStat ls = new LaborStat();
				Object[] obj = (Object[])list.get(i);
				ls.setProjectName(p.getProjectName());
				ls.setWorkTime(obj[0]+"");
				ls.setCurrentPerson(obj[1]+"" );
				ls.setSection(obj[2]+"");
				ls.setProjectProcess(obj[3]+"");
			lssStat.add(ls);
		}
		return lssStat;
	}

	/**
	 * 当项目为具体某个的时候
	 * @param projectNameBySection
	 * @param section
	 * @param sectionHours
	 * @param sectionPerson
	 * @return
	 */
	public List<LaborStat> findLaborStatBySectionAndPid(
			Project project, String section, String sectionHours,
			String sectionPerson) {
		String hql = "select sum(hours),sum(locale),section,projectProcess from Labor where projectid = ?";
		
		if("所有".equals(section)){
			if("".equals(sectionPerson)){
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}else{
				hql = hql + " and sum(locale) = '" + sectionPerson + "' ";
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}
		}else{
			hql = hql + " and section = '" + section + "'";
			if("".equals(sectionPerson)){
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}else{
				hql = hql + " and sum(locale) = '" + sectionPerson + "' ";
				if("".equals(sectionHours)){
					hql = hql + " group by projectProcess";
				}else{
					hql = hql + " and sum(hours) = '" + sectionHours + "' group by projectProcess";
				}
			}
		}
		Query query = getSession().createQuery(hql);
		query.setParameter(0, project.getId());
		
		@SuppressWarnings("rawtypes")
		List list= query.list();
		
		
		List<LaborStat> lssStat = new ArrayList<LaborStat>();
		for(int i = 0; i < list.size(); i++){
			LaborStat ls = new LaborStat();
				Object[] obj = (Object[])list.get(i);
				ls.setProjectName(project.getProjectName());
				ls.setWorkTime(obj[0]+"");
				ls.setCurrentPerson(obj[1]+"" );
				ls.setSection(obj[2]+"");
				ls.setProjectProcess(obj[3]+"");
			lssStat.add(ls);
		}
		return lssStat;
	}

	/**
	 * 根据项目id去找工时
	 * @param pid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findLaborByProjectId(int pid) {

		String hql = "from Labor where project = ?";
		Query query = getSession().createQuery(hql);
		query.setInteger(0, pid);
		
		return query.list();
	}

	
	/**
	 * 默认查找两周内的工时,以工时日期查询
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Labor> findAllLaborInOneMonth() {
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		Calendar calendar = Calendar.getInstance();
	    calendar.setTime(date);
	    calendar.add(Calendar.DATE,-30);
	    Date date2 = calendar.getTime();
		
		String endtime = df.format(date);
		String begintime = df.format(date2);
		
		String hql = "from Labor where datetime >= '"+begintime+"' and datetime <= '"+endtime+"'";
		Query query = getSession().createQuery(hql);
		
		return query.list();
	}


}
